Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Imports System.Data
Imports DataAccesGenerales
Imports DataAcces

Public Class DataAccesPlaneacion_CargaSuelta
  Private Const consTimeOut As Integer = 0 
  
  Public Function Planeacion_CargaSuelta_Insertar(pnid As Integer, pnidPlaneacion As Integer, pnidProducto As Integer, pcantidad As Integer, pnidEmbalaje As Integer, pnidPuerto As Integer, pnidArea As Integer, pganchos As Integer, ptipo_Maniobra As String, ptoneladas As Integer, pnidEmpresas As Integer, pnidEmpresaMan As Integer) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Planeacion_CargaSueltaInsertar" 
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pnidPlaneacion", pnidPlaneacion)
          oraCommandDocumentos.Parameters.Add("pnidProducto", pnidProducto)
          oraCommandDocumentos.Parameters.Add("pcantidad", pcantidad)
          oraCommandDocumentos.Parameters.Add("pnidEmbalaje", pnidEmbalaje)
          oraCommandDocumentos.Parameters.Add("pnidPuerto", pnidPuerto)
          oraCommandDocumentos.Parameters.Add("pnidArea", pnidArea)
          oraCommandDocumentos.Parameters.Add("pganchos", pganchos)
          oraCommandDocumentos.Parameters.Add("ptipo_Maniobra", ptipo_Maniobra)
          oraCommandDocumentos.Parameters.Add("ptoneladas", ptoneladas)
          oraCommandDocumentos.Parameters.Add("pnidEmpresas", pnidEmpresas)
          oraCommandDocumentos.Parameters.Add("pnidEmpresaMan", pnidEmpresaMan)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function

  Public Function Planeacion_CargaSuelta_Modificar(pnid As Integer, pnidPlaneacion As Integer, pnidProducto As Integer, pcantidad As Integer, pnidEmbalaje As Integer, pnidPuerto As Integer, pnidArea As Integer, pganchos As Integer, ptipo_Maniobra As String, ptoneladas As Integer, pnidEmpresas As Integer, pnidEmpresaMan As Integer) As Byte
    Dim pTransaccion As Byte = 0
    Dim mensaje As String = String.Empty
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Planeacion_CargaSueltaModificar"
          oraCommandDocumentos.Parameters.Add("pnid", pnid)
          oraCommandDocumentos.Parameters.Add("pnidPlaneacion", pnidPlaneacion)
          oraCommandDocumentos.Parameters.Add("pnidProducto", pnidProducto)
          oraCommandDocumentos.Parameters.Add("pcantidad", pcantidad)
          oraCommandDocumentos.Parameters.Add("pnidEmbalaje", pnidEmbalaje)
          oraCommandDocumentos.Parameters.Add("pnidPuerto", pnidPuerto)
          oraCommandDocumentos.Parameters.Add("pnidArea", pnidArea)
          oraCommandDocumentos.Parameters.Add("pganchos", pganchos)
          oraCommandDocumentos.Parameters.Add("ptipo_Maniobra", ptipo_Maniobra)
          oraCommandDocumentos.Parameters.Add("ptoneladas", ptoneladas)
          oraCommandDocumentos.Parameters.Add("pnidEmpresas", pnidEmpresas)
          oraCommandDocumentos.Parameters.Add("pnidEmpresaMan", pnidEmpresaMan)

          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add("pMensaje", Oracle.DataAccess.Client.OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output

          oraConnection.Open()
          oraCommandDocumentos.ExecuteNonQuery()
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          mensaje = oraCommandDocumentos.Parameters("pMensaje").Value.ToString()
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return pTransaccion
  End Function
  
  Function Planeacion_CargaSuelta_Consultar(nid as Integer) As DataTable
    Dim dtResultado As New DataTable("Planeacion_CargaSueltaConsulta")
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Planeacion_CargaSueltaConsultar" 
          oraCommandDocumentos.Parameters.Add("pnid", nid)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          oraDataAdapter.Fill(dtResultado)
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dtResultado
  End Function
  
  Function Planeacion_CargaSuelta_Combo(pNombre As String) As DataTable
    Dim dtResultado As New DataTable("Planeacion_CargaSueltaCombo")
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Planeacion_CargaSueltaCombo" 
		  oraCommandDocumentos.Parameters.Add("pNombre", pNombre)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output

          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          oraDataAdapter.Fill(dtResultado)
          oraConnection.Close()
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dtResultado
  End Function
  
  Public Function Planeacion_CargaSuelta_CadenaOriginalXMLChilkat(nidDocumento As Integer) As Chilkat.Xml
    Dim detalleDataSet As New DataSet("DatosCaseta")
    Try
      detalleDataSet = Planeacion_CargaSuelta_DetalleXML(nidDocumento)
	  'En esta parte van las tablas obtenidas en la consulta anterior
	  'separadas dependiendo del reporte a crear, ejemplo:
	  detalleDataSet.Tables(0).TableName = "Informacion del Tramite"
      detalleDataSet.Tables(1).TableName = "Datos Planeacion_CargaSuelta"
      detalleDataSet.Tables(2).TableName = "Archivos Anexos"
      detalleDataSet.Tables(3).TableName = "Incidencias"

      Dim xml As New Chilkat.Xml
      xml.LoadXml(detalleDataSet.GetXml())
      Return xml 'detalleDataSet.GetXml()
    Catch ex As Exception
      Throw ex
    End Try
  End Function

  Public Function Planeacion_CargaSuelta_DetalleXML(ByVal nidDocumento As Integer) As DataSet
    Dim dsDetalle As New DataSet("Planeacion_CargaSueltaDetalle")
    Dim pTransaccion As Byte = 0
    Try
      Using oraConnection As New Oracle.DataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings("EDIV2DESOraClient").ConnectionString)
        Using oraCommandDocumentos As New Oracle.DataAccess.Client.OracleCommand()
          oraCommandDocumentos.Connection = oraConnection
          oraCommandDocumentos.CommandTimeout = consTimeOut
          oraCommandDocumentos.CommandType = CommandType.StoredProcedure
          oraCommandDocumentos.CommandText = "EDIV2.MI_EDIV2GENERALES.Planeacion_CargaSueltaDetalleXML"
          oraCommandDocumentos.Parameters.Add("pNidDocumento", nidDocumento)
          oraCommandDocumentos.Parameters.Add("pTransaccion", Oracle.DataAccess.Client.OracleDbType.Byte).Direction = ParameterDirection.Output
          'En esta parte es para los cursores necesario 
		  oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pEncabezado", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pDatos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pAnexos", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
          oraCommandDocumentos.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pIncidencias", OracleDbType.RefCursor)).Direction = ParameterDirection.Output
		
          Dim oraDataAdapter As New OracleDataAdapter(oraCommandDocumentos)
          pTransaccion = CByte(oraCommandDocumentos.Parameters("pTransaccion").Value.ToString())
          oraDataAdapter.Fill(dsDetalle)
        End Using
      End Using
    Catch ex As Exception
      Throw ex
    End Try
    Return dsDetalle
  End Function
  
End Class
